<?php
	function f420($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("420");

		$dataType = getValue($_GET['dataType']);
		$action = getValue($_GET['action']);

		if ($dataType == "json"){
			$format = array(
				"StartTime" => "dateY-m-d H:i:s",
				"EndTime" => "dateY-m-d H:i:s",
			);

			$dataHandle->setFormat($format);
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$filter = $dataHandle->processFilters($startTime, $endTime, "tbl","start_time");
				$query = getQuery($startTime, $endTime, $filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else if($action == "getLines"){
			$query = getLines();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "420: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);

	}

	function getQuery(&$startTime, &$endTime, &$filter){
		$queryString = "
			with tbl as(
				SELECT
					line_id,
					RIGHT(REPLICATE('0', 7) + LTRIM(RTRIM(product_id)), 7)   as product_id,
					start_time,
					end_time,
					description
				FROM lin_produced_export_view

				union

				SELECT
					line_id,
					RIGHT(REPLICATE('0', 7) + LTRIM(RTRIM(product_id)), 7)   as product_id,
					start_time,
					end_time,
					description
				FROM lin_produced_changeovers_export_view
			)

			SELECT
				line_id AS 'Line',
				product_id AS 'Item#',
				start_time AS 'StartTime',
				end_time AS 'EndTime',
				description AS 'Description'
			FROM tbl";


		$queryString = $queryString.$filter;

		//min(start_time) AS 'StartTime',
		//max(end_time) AS 'EndTime',
		//$queryString = $queryString." group by line_id,product_id,description ";

		$queryString = $queryString." order by 'StartTime' desc;";

		return $queryString;
	}
	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, date) AS availableYear 
			FROM lin_produced_changeovers_export_view 
			ORDER BY availableYear desc";
		return $queryString;
	}
	function getLines(){
		$queryString = 
			"SELECT DISTINCT 
				line_id AS availableLines
			FROM lin_produced_changeovers_export_view 
			order by availableLines asc";
		return $queryString;
	}
?>